#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
Database migration script to fix schema mismatches
"""

import sys
from pathlib import Path

# Add project root to Python path
project_root = Path(__file__).parent.absolute()
sys.path.insert(0, str(project_root))

def migrate_database():
    """Migrate database to fix schema issues"""
    try:
        from backend.config.database import engine
        from sqlalchemy import text, inspect
        
        print("🔄 正在检查和修复数据库模式...")
        
        # Get current database state
        inspector = inspect(engine)
        
        # Check weather_daily table
        if 'weather_daily' in inspector.get_table_names():
            print("🔍 检查 weather_daily 表结构...")
            columns = [col['name'] for col in inspector.get_columns('weather_daily')]
            print(f"   现有列: {columns}")
            
            # Check for missing columns that WeatherDaily model expects
            required_columns = [
                ('ws_max', 'FLOAT', '最大风速'),
                ('pressure', 'FLOAT', '气压'), 
                ('uv_index', 'FLOAT', '紫外线指数'),
                ('data_source', 'VARCHAR(20)', '数据源'),
                ('updated_at', 'DATETIME', '更新时间')
            ]
            
            missing_columns = []
            for col_name, col_type, comment in required_columns:
                if col_name not in columns:
                    missing_columns.append((col_name, col_type, comment))
            
            if missing_columns:
                print(f"❌ 缺少列: {[col[0] for col in missing_columns]}")
                print("🔧 正在添加缺少的列...")
                
                with engine.connect() as conn:
                    for col_name, col_type, comment in missing_columns:
                        try:
                            sql = f"ALTER TABLE weather_daily ADD COLUMN {col_name} {col_type} COMMENT '{comment}'"
                            conn.execute(text(sql))
                            print(f"   ✅ 添加列: {col_name}")
                        except Exception as e:
                            print(f"   ⚠️  添加列 {col_name} 失败: {e}")
                    
                    conn.commit()
                    
            else:
                print("✅ weather_daily 表结构正确!")
        else:
            print("❌ weather_daily 表不存在，正在创建...")
            # Import and create all tables
            from backend.entities.weather_daily import WeatherDaily
            from backend.config.database import Base
            Base.metadata.create_all(bind=engine)
            print("✅ 已创建 weather_daily 表!")
        
        # Verify the fix
        print("\n🔍 验证修复结果...")
        updated_columns = [col['name'] for col in inspector.get_columns('weather_daily')]
        print(f"   更新后的列: {updated_columns}")
        
        # Test a simple query
        with engine.connect() as conn:
            result = conn.execute(text("SELECT COUNT(*) as count FROM weather_daily"))
            count = result.first().count
            print(f"✅ weather_daily 表查询成功，包含 {count} 条记录")
            
        print("🎉 数据库迁移完成!")
        
    except Exception as e:
        print(f"❌ 数据库迁移失败: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

if __name__ == "__main__":
    migrate_database()